package jsyf.oa.web.until;

import com.alibaba.fastjson.JSONObject;
import jsyf.com.util.ExportExcel;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2018/4/27 0027.
 */
public class ExportSignInExcel {
    private final Logger logger = LoggerFactory.getLogger(ExportExcel.class);
    //显示的导出表的标题
    private String title;
    //导出表的列名
    private Object[] rowName;

    private List<Object[]> dataList = new ArrayList<Object[]>();

    private HttpServletResponse response;

    private String backGroud;
    private List<JSONObject> jsonObjectList;
    private String dateTime;

    //构造方法，传入要导出的数据
    public ExportSignInExcel(String title, Object[] rowName, List<Object[]> dataList,
                             HttpServletResponse response, String backGroud, List<JSONObject> jsonObjectList,
                             String dateTime) {
        this.dataList = dataList;
        this.rowName = rowName;
        this.title = title;
        this.response = response;
        this.backGroud = backGroud;
        this.jsonObjectList = jsonObjectList;
        this.dateTime = dateTime;
    }

    /*
    * 导出数据
    * */
    public void export() throws Exception {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(); // 创建一个excel对象
            HSSFSheet sheet = workbook.createSheet(title); // 创建表格
            // 产生表格标题行
            sheet.setDefaultRowHeightInPoints(20);
            HSSFRow rowm = sheet.createRow(0);  // 行
            HSSFCell cellTiltle = rowm.createCell(0);  // 单元格

            //创建本sheet的HSSFPatriarch

            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

            // sheet样式定义
            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook); // 头样式
            HSSFCellStyle columnStyle = this.getColumnStyle(workbook); // 头样式
            HSSFCellStyle lieStyle = this.getLieStyle(workbook); // 头样式
            HSSFCellStyle style = this.getStyle(workbook);  // 单元格样式
            HSSFCellStyle styleFont = this.getStyleFont(workbook);  // 单元格样式
            /**
             * 参数说明
             * 从0开始   第一行 第一列 都是从角标0开始
             * 行 列 行列    (0,0,0,5)  合并第一行 第一列  到第一行 第六列
             * 起始行，起始列，结束行，结束列
             *
             * new Region()  这个方法使过时的
             */
            // 合并第一行的所有列
            sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (rowName.length - 1)));
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue(title);

            int columnNum = rowName.length;  // 表格列的长度
            HSSFRow rowRowName = sheet.createRow(1);  // 在第二行创建行
            HSSFCellStyle cells = workbook.createCellStyle();
            cells.setBottomBorderColor(HSSFColor.BLACK.index);
            rowRowName.setRowStyle(cells);

            // 循环 将列名放进去
            for (int n = 0; n < columnNum; n++) {
                HSSFCell cellRowName = rowRowName.createCell(n);                //创建列头对应个数的单元格
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);                //设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString((String) rowName[n]);
                cellRowName.setCellValue(text);
                if (rowName[n].toString() != null) {
                    if ("012345678910111213141516171819202122232425262728293031".contains(rowName[n].toString())) {
                        String strRows = unitFormat(Integer.parseInt(rowName[n].toString()));
                        sheet.setColumnWidth(n, 5 * 256);
                        if (backGroud.contains(strRows)) {
                            //设置列头单元格的值
                            cellRowName.setCellStyle(columnStyle);
                        } else {
                            //设置列头单元格的值
                            cellRowName.setCellStyle(columnTopStyle);
                        }
                    } else {
                        cellRowName.setCellStyle(columnTopStyle);
                        sheet.setColumnWidth(n, 10 * 256);
                    }
                }
            }
            // 将查询到的数据设置到对应的单元格中
            for (int i = 0; i < dataList.size(); i++) {
                Object[] obj = dataList.get(i);//遍历每个对象
                HSSFRow row = sheet.createRow(i + 2);//创建所需的行数
                for (int j = 0; j < obj.length; j++) {
                    HSSFCell cell = null;   //设置单元格的数据类型
                    cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                    Boolean isNum = false;//data是否为数值型
                    Boolean isInteger = false;//data是否为整数
                    Boolean isPercent = false;//data是否为百分数
                    //是否为本月离职人员
                    JSONObject userList = jsonObjectList.get(i);
                    cell.setCellStyle(style);
                    if (obj[j] != null || "".equals(obj[j])) {
                        //判断data是否为数值型
                        isNum = obj[j].toString().matches("^(-?\\d+)(\\.\\d+)?$");
                        //判断data是否为整数（小数部分是否为0）
                        isInteger = obj[j].toString().matches("^[-\\+]?[\\d]*$");
                        //判断data是否为百分数（是否包含“%”）
                        isPercent = obj[j].toString().contains("%");
                    }
                    if (isNum && !isPercent) {
                        HSSFDataFormat df = workbook.createDataFormat(); // 此处设置数据格式
                        if (isInteger) {
                            cells.setDataFormat(df.getBuiltinFormat("#,#0"));//数据格式只显示整数
                        } else {
                            cells.setDataFormat(df.getBuiltinFormat("#,##0.00"));//保留两位小数点
                        }
                        // 设置单元格内容为double类型
                        cell.setCellValue(Double.parseDouble(obj[j].toString()));
                        //addComment(patriarch, cell,  "离职");
                    } else {
                        // 设置单元格内容为字符型
                        cell.setCellValue(obj[j].toString());
                    }
                    if (columnNum == obj.length) {
                        if (rowName[j].toString() != null) {
                            if ("012345678910111213141516171819202122232425262728293031".contains(rowName[j].toString())) {
                                String strRows = unitFormat(Integer.parseInt(rowName[j].toString()));
                                if (backGroud.contains(strRows)) {
                                    //设置列头单元格的值
                                    cell.setCellStyle(lieStyle);
                                }
                                String rowDateTime = dateTime + "-" + strRows;
                                if (StringUtils.isNotBlank(userList.getString("entryDate"))) {
                                    if (userList.getString("entryDate").equals(rowDateTime)) {
                                        addComment(patriarch, cell, "入职");
                                    }
                                }
                                if (StringUtils.isNotBlank(userList.getString("leaveDate"))) {
                                    if (userList.getString("leaveDate").equals(rowDateTime)) {
                                        addComment(patriarch, cell, "离职");
                                    }
                                }
                            }
                        }
                    }
                }
            }
            //合并公司
            sheet.addMergedRegion(new CellRangeAddress(2, dataList.size() + 1, 1, 1));

            //合并部门
            int departRow = 0;
            String departValue = "";

            System.out.println("sheet.getLastRowNum()=" + sheet.getLastRowNum());
            for (int i = 2; i <= sheet.getLastRowNum() + 1; i++) {
                HSSFRow rows = sheet.getRow(i);
                HSSFCell cells0 = null;
                if (rows != null) {
                    cells0 = rows.getCell(2);
                }
                if (cells0 == null || !cells0.getStringCellValue().equals(departValue)) {
                    if (StringUtils.isNotBlank(departValue)) {
                        sheet.addMergedRegion(new CellRangeAddress(departRow, i - 1, 2, 2));
                        if (rows!=null){
                            HSSFCell cellsV = rows.getCell(2);
                            cellsV.setCellStyle(style);
                        }
                    }
                    if (cells0 != null) {
                        departRow = i;
                        departValue = cells0.getStringCellValue();
                    }
                }
            }

            //合并姓名
            int firstRow = 0;
            String tempValue = "";
            for (int i = 2; i <= sheet.getLastRowNum() + 1; i++) {
                HSSFRow rows = sheet.getRow(i);
                HSSFCell cells0 = null;
                if (rows != null) {
                    cells0 = rows.getCell(3);
                }
                if (cells0 == null || !cells0.getStringCellValue().equals(tempValue)) {
                    if (StringUtils.isNotBlank(tempValue)) {
                        sheet.addMergedRegion(new CellRangeAddress(firstRow, i - 1, 3, 3));
                        if (rows!=null){
                            HSSFCell cellsV = rows.getCell(3);
                            cellsV.setCellStyle(style);
                        }
                    }
                    if (cells0 != null) {
                        firstRow = i;
                        tempValue = cells0.getStringCellValue();
                    }
                }
            }


            //  让列宽随着导出的列长自动适应
            sheet.autoSizeColumn(1);

            sheet.autoSizeColumn(1, true);
            // 第七步，将文件输出到客户端浏览器
            if (workbook != null) {
                try {
                    // excel 表文件名
                    String fileName = title + ".xls";
                    String fileName11 = URLEncoder.encode(fileName, "UTF-8");
                    String headStr = "attachment; filename=\"" + fileName11 + "\"";
                    response.setContentType("APPLICATION/OCTET-STREAM");
                    response.setHeader("Content-Disposition", headStr);
                    OutputStream out = response.getOutputStream();
                    workbook.write(out);
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("导出信息失败！");
        }
    }

    /*
     * 列头单元格样式
     */
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 10);
        //字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("微软雅黑");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();

        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //设置单元格背景颜色
        style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        return style;

    }

    public HSSFCellStyle getColumnStyle(HSSFWorkbook workbook) {

        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 10);
        //字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("微软雅黑");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置前景填充样式
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //前景填充色
        style.setFillForegroundColor(HSSFColor.YELLOW.index);
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;

    }


    public HSSFCellStyle getLieStyle(HSSFWorkbook workbook) {

        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 8);
        //字体加粗
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("微软雅黑");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置前景填充样式
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //前景填充色
        style.setFillForegroundColor(HSSFColor.YELLOW.index);
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;

    }

    /*
   * 列数据信息单元格样式
   */
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 8);
        font.setColor(HSSFColor.BLACK.index);
        //设置字体名字
        font.setFontName("微软雅黑");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;

    }


    public HSSFCellStyle getStyleFont(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 8);
        //设置字体名字
        font.setFontName("微软雅黑");
        //设置字体颜色
        font.setColor(HSSFColor.RED.index);
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;

    }

    public static String unitFormat(int i) {
        String retStr = null;
        if (i >= 0 && i < 10) {
            retStr = "0" + Integer.toString(i);
        } else {
            retStr = "" + i;
        }
        return retStr;
    }

    /**
     * 设置单元格注释
     *
     * @param patriarch
     * @param cell
     * @param cellnum
     */

    public static void addComment(HSSFPatriarch patriarch, HSSFCell cell, String cellnum) {

        HSSFComment comment = patriarch.createComment(
                new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 2, (short) 4, 4));

        comment.setString(new HSSFRichTextString(cellnum));

        cell.setCellComment(comment);

    }


}
